package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import model.Guest;
import model.Room;
import model.RoomReservation;
import model.RoomType;

public class DbRoom {
	private Connection con;
	
	public DbRoom() {
		con = DbConnection.getInstance().getDBcon();
	}
	 public ArrayList<Room> getEmptyRooms(String wClause1, String wClause2)
	    {
	        return miscWhere(wClause1, wClause2);
	    }
	 public ArrayList<Room> getAllRooms(boolean retriveAssociation) {
			
			return miscWhere2("", retriveAssociation);
			
		}
	 
	 private ArrayList<Room> miscWhere(String wClause1,String wClause2)
		{
	            ResultSet results;
		    ArrayList<Room> list = new ArrayList<Room>();	
			
		    String query =  buildQuery(wClause1, wClause2);
	  
	            try{ // read the employee from the database
			Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	
		 	results = stmt.executeQuery(query);
		 	
		
			while( results.next() ){
		     	 Room empObj = new Room();
			 empObj = buildRoom(results);	
	                 list.add(empObj);	
			}//end while
	                 stmt.close();
	                 
				
			}//slut try	
		 	catch(Exception e){
		 		System.out.println("Query exception - select: "+e);
				e.printStackTrace();
		 	}
			return list;
		}
	 
	 private ArrayList<Room> miscWhere2(String wClause, boolean retrieveAssociation)
	 {
	         ResultSet results;
	     ArrayList<Room> list = new ArrayList<Room>();	
	 	
	     String query =  buildQuery2(wClause);

	         try{ // read the employee from the database
	 	Statement stmt = con.createStatement();
	  	stmt.setQueryTimeout(5);
	  	
	  	results = stmt.executeQuery(query);
	  	
	  	

	 	while( results.next() ){
	      	 Room oObj = new Room();
	 	 oObj = buildRoom(results);	
	              list.add(oObj);	
	 	}//end while
	              stmt.close();
	          
	         }
	  	catch(Exception e){
	  		System.out.println("Query exception - select: "+e);
	 		e.printStackTrace();
	  	}
	 	return list;
	 }
	 
	 
	 
	//method to build the query
		private String buildQuery(String wClause1, String wClause2)
		{
		    String query="SELECT Room.id, roomType  FROM Room, RoomType";
			
			if (wClause1.length()>0 && wClause2.length()>0)
				query=query+  " WHERE Room.typeId = RoomType.ID and Room.ID not in(select roomId from RRbuffer, roomreservation where RResId = RoomReservation.ID and RoomReservation.checkOut >= '" + wClause1 + "' and '" + wClause2 +  "' >= RoomReservation.checkIn)";
				
			return query;
		}
		
		//method to build the query
				private String buildQuery2(String wClause)
				{
				    String query="SELECT id  FROM Room";
					
					if (wClause.length()>0 )
						query=query+  "WHERE Room.typeId = RoomType.ID"  ;
						
					return query;
				}
		
		private Room buildRoom(ResultSet results)
	    {   Room rObj = new Room();
	        try{ // the columns from the table emplayee  are used
	              rObj.setId(results.getInt("id"));
	              rObj.setType(new RoomType(results.getString("roomType")));
	              
	        }
	       catch(Exception e)
	       {
	           System.out.println("error in building the Room object");
	       }
	       return rObj;
		

	}
		
		

}
